﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CMP.ERP.Model;
using CMP.ERP.Helper;
using System.Data;
using System.Data.SqlClient;

namespace CMP.ERP.DAL
{
    public class PorBuyDAL
    {
        /// <summary>
        /// 实例化对象
        /// </summary>
        SQLHelper p = new SQLHelper();
        //连接数据库字符串
        string connSql = "Data Source=169.254.4.161;Initial Catalog=FourGroupERP;User ID=sa;Pwd=111111";
        /// <summary>
        /// 显示采购订单信息
        /// </summary>
        /// <returns></returns>
        public List<PorBuy> Porshow()
        {
            string sql = "select Pid, PorBuy_Date,PorBuy_Cid,PorBuy_SumMoney,PorBuy_LastTime,Por_Status,PorBuy.Supplier_Id,PorBuy.WareHose_Id,Supplier_Name,WareHose_Name from PorBuy inner join Supplier on PorBuy.Supplier_Id=Supplier.Supplier_Id inner join WareHose on PorBuy.WareHose_Id=WareHose.WareHose_Id order by PorBuy_Date DESC";

            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<PorBuy> list = new List<PorBuy>();
            foreach (DataRow item in table.Rows)
            {
                PorBuy s = new PorBuy();
                s.Pid = Convert.ToInt32(item["Pid"]);
                s.PorBuy_Date = Convert.ToDateTime(item["PorBuy_Date"]);
                s.PorBuy_Cid = item["PorBuy_Cid"].ToString();
                s.PorBuy_SumMoney = Convert.ToDecimal(item["PorBuy_SumMoney"]);
                s.PorBuy_LastTime = Convert.ToDateTime(item["PorBuy_LastTime"]);
                s.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                s.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                s.Supplier_Name = item["Supplier_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Por_Status = Convert.ToInt32(item["Por_Status"]);
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 根据年月日查询
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Month"></param>
        /// <param name="Day"></param>
        /// <returns></returns>
        public DataTable SelPorBuy(string StartTime,string End_Time)
        {
            string str = "select Pid, PorBuy_Date,PorBuy_Cid,PorBuy_SumMoney,PorBuy_LastTime,PorBuy.Supplier_Id,PorBuy.WareHose_Id,Supplier_Name,WareHose_Name from PorBuy inner join Supplier on PorBuy.Supplier_Id=Supplier.Supplier_Id inner join WareHose on PorBuy.WareHose_Id=WareHose.WareHose_Id where PorBuy.PorBuy_LastTime between '" + StartTime + "' and '" + End_Time + "'";
            return p.ExecDataTable(str);
        }
        /// <summary>
        /// 添加采购订单信息
        /// </summary>
        /// <param name="model"></param>
        /// <param name="list"></param>
        public List<int> Add(PorBuy model, List<PorBuyMate> list)
        {
            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            foreach (var item in list)
            {
                model.PorBuy_SumMoney += item.Commodity_Price * item.Por_num;

                model.Por_Status = 0;
            }
            StringBuilder stbu = new StringBuilder();

            stbu.Append("insert into PorBuy(PorBuy_Date,PorBuy_Cid,PorBuy_SumMoney,PorBuy_LastTime,Supplier_Id,WareHose_Id,Por_Status)");
            stbu.Append(" values(@PorBuy_Date,@PorBuy_Cid,@PorBuy_SumMoney,@PorBuy_LastTime,@Supplier_Id,@WareHose_Id,@Por_Status);select @@identity;");

            SqlParameter[] parameters = {
                 new SqlParameter("@PorBuy_Date", SqlDbType.DateTime),
                 new SqlParameter("@PorBuy_Cid", SqlDbType.VarChar,100),
                 new SqlParameter("@PorBuy_SumMoney", SqlDbType.Decimal),
                 new SqlParameter("@PorBuy_LastTime", SqlDbType.DateTime),
                 new SqlParameter("@Supplier_Id", SqlDbType.Int),
                 new SqlParameter("@WareHose_Id", SqlDbType.Int),
                 new SqlParameter("@Por_Status",SqlDbType.Int)
                                        };
            parameters[0].Value = DateTime.Now;
            parameters[1].Value = model.PorBuy_Cid;
            parameters[2].Value = model.PorBuy_SumMoney;
            parameters[3].Value = DateTime.Now;
            parameters[4].Value = model.Supplier_Id;
            parameters[5].Value = model.WareHose_Id;
            parameters[6].Value = model.Por_Status;

             int Pid = Convert.ToInt32(p.ExecScalar(stbu.ToString(), parameters));
            //Modify End

            //子表添加数据
            //Author: JiXiang 20120419
            //Modify Begin
            StringBuilder strSqlSub = new StringBuilder();
            strSqlSub.Append("insert into PorBuyMate(");
            strSqlSub.Append("Pid,Commodity_Id,Por_num) ");
            strSqlSub.Append(" values (@Pid,@Commodity_Id,@Por_num)");
            if (list != null)
            {
                foreach (PorBuyMate m in list)
                {
                    m.Pid = Pid;
                    SqlParameter[] parametersSub = {
                    new SqlParameter("@Pid", SqlDbType.Int),
                    new SqlParameter("@Commodity_Id", SqlDbType.Int),
                    new SqlParameter("@Por_num", SqlDbType.Int),                            
                                                    };
                    parametersSub[0].Value = m.Pid;
                    parametersSub[1].Value = m.Commodity_Id;
                    parametersSub[2].Value = m.Por_num;
                    cmdTextList.Add(strSqlSub.ToString());
                    cmdParametersList.Add(parametersSub.ToList<SqlParameter>());
                }
            }
            return p.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 删除采购订单
        /// </summary>
        /// <param name="Pid"></param>
        /// <returns></returns>
        public List<int>del(int Pid)
        {

            List<string> cmdTextList = new List<string>();
            List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
            //确认字表中是否有数据
            StringBuilder getstrSql = new StringBuilder();
            getstrSql.Append("select PorMaid,PorBuyMate.Commodity_Id,Por_num,PorBuyMate.Pid,PorBuy_Date,PorBuy_Cid,PorBuy_SumMoney,Supplier_Id,WareHose_Id ,PorBuy_LastTime,Commodity.Commodity_Name");
            getstrSql.Append("  from PorBuyMate join PorBuy on PorBuy.Pid=PorBuyMate.Pid ");
            getstrSql.Append("  join Commodity on PorBuyMate.Commodity_Id=Commodity.Commodity_Id where PorBuyMate.Pid=@Pid");
            SqlParameter[] pareameter ={
                                         new SqlParameter("@Pid",SqlDbType.Int)
                                     };
            pareameter[0].Value = Pid;

            DataTable dt = p.ExecDataTable(getstrSql.ToString(), pareameter);
            List<PorBuyMate> list = new List<PorBuyMate>();
            foreach (DataRow item in dt.Rows)
            {
                PorBuyMate m = new PorBuyMate();
                m.Pid = Convert.ToInt32(item["Pid"]);
                m.Por_num = Convert.ToInt32(item["Por_num"]);
                m.PorMaid = Convert.ToInt32(item["PorMaid"]);
                m.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                list.Add(m);
            }
            if(list.Count!=0)
            {
                //如果该子表中有数据则删除子表信息
                StringBuilder strsub = new StringBuilder();
                strsub.Append("delete from PorBuyMate");
                strsub.Append("  where Pid=@Pid");
                SqlParameter[] ParameterSub ={
                                          new SqlParameter("@Pid",SqlDbType.Int),
                                          };
                ParameterSub[0].Value = Pid;
                cmdTextList.Add(strsub.ToString());
                cmdParametersList.Add(ParameterSub.ToList<SqlParameter>());
            }
            //删除主表信息
            StringBuilder strsubs = new StringBuilder();
            strsubs.Append("delete from PorBuy");
            strsubs.Append("  where Pid=@Pid");
            SqlParameter[] ParameterSubs ={
                                          new SqlParameter("@Pid",SqlDbType.Int),
                                          };
            ParameterSubs[0].Value = Pid;
            cmdTextList.Add(strsubs.ToString());
            cmdParametersList.Add(ParameterSubs.ToList<SqlParameter>());

            return p.ExecTransaction(cmdTextList, cmdParametersList);
        }
        /// <summary>
        /// 根据采购id编号获取采购详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
       public DataTable ShowXiangqing2(int id)
        {
            string ProcName = "Proc_CaigouXiangqing2";
            SqlParameter[] par = new SqlParameter[1]
           {
               new SqlParameter("@pid",id)
           };
           return p.ExecDataTable_Proc(ProcName,par);
        }
       public DataTable ShowXiangqing1(int id)
       {
           string ProcName = "Proc_CaigouXiangqing1";
           SqlParameter[] par = new SqlParameter[1]
           {
               new SqlParameter("@pid",id)
           };
           return p.ExecDataTable_Proc(ProcName, par);
       }
        /// <summary>
        /// 入库
        /// </summary>
        /// <param name="Commodity_Id"></param>
        /// <param name="WareHose_Id"></param>
        /// <param name="Pid"></param>
        /// <returns></returns>
       public int IntoStock(int Commodity_Id, int WareHose_Id, string Commodity_Name, string WareHose_Name, int PorMaid)
       {
           string ProcName = "Proc_AddStock1";
           SqlParameter[] par = new SqlParameter[] 
           {
                new SqlParameter("@Commodity_Id",Commodity_Id),
                new SqlParameter("@WareHose_Id",WareHose_Id),
                new SqlParameter("@PorMaid",PorMaid),
                new SqlParameter("@Commodity_Name",Commodity_Name),
                new SqlParameter("@WareHose_Name",WareHose_Name)
           };
           return p.ExecNonquery_Proc(ProcName, par);
       }

       /// <summary>
       /// 审核入库
       /// </summary>
       /// <param name="StockOrderID"></param>
       /// <param name="listPurchaseOrderSheetInfo"></param>
       /// <returns></returns>
       public List<int> Update(int Pid, List<PorBuyMate> listpor)
       {
           List<string> cmdTextList = new List<string>();
           List<List<SqlParameter>> cmdParametersList = new List<List<SqlParameter>>();
           #region 采购订单子表删除
           //采购订单子表删除
           //StringBuilder delstrSqlSub = new StringBuilder();
           //delstrSqlSub.Append("delete from PorBuyMate ");
           //delstrSqlSub.Append("  where Pid=@Pid");

           //SqlParameter[] delparametersSub = { 
           //                              new SqlParameter("@Pid",SqlDbType.Int)  
           //                                };
           //delparametersSub[0].Value = Pid;

           //cmdTextList.Add(delstrSqlSub.ToString());
           //cmdParametersList.Add(delparametersSub.ToList<SqlParameter>());
           #endregion
           #region 采购订单子表重填数据
           ////采购订单子表重填数据
           ////Author: JiXiang 20120419
           ////Modify Begin
           //StringBuilder addstrSqlSub = new StringBuilder();
           //addstrSqlSub.Append("insert into PorBuyMate(");
           //addstrSqlSub.Append("Pid,Commodity_Id,Por_num) ");
           //addstrSqlSub.Append(" values (@Pid,@Commodity_Id,@Por_num)");
           ////非空判定
           ////Author: ZhaoLiang 22012-04-25
           //if (listpor != null)
           //{
           //    foreach (PorBuyMate m in listpor)
           //    {
           //        m.Pid = Pid;
           //        SqlParameter[] addparametersSub = {
           //         new SqlParameter("@Pid", SqlDbType.Int),
           //         new SqlParameter("@Commodity_Id", SqlDbType.Int),
           //         new SqlParameter("@Por_num", SqlDbType.Int),  
           //                                            };
           //        addparametersSub[0].Value = m.Pid;
           //        addparametersSub[1].Value = m.Commodity_Id;
           //        addparametersSub[2].Value = m.Por_num;
           //        //Modify End
           //        cmdTextList.Add(addstrSqlSub.ToString());
           //        cmdParametersList.Add(addparametersSub.ToList<SqlParameter>());
           //    }
           //}
           #endregion
           //根据采购订单主表编号获取仓库编号和子表的商品编号和商品数量 根据商品编号和仓库编号进行库存信息的增加

           StringBuilder updInvet = new StringBuilder();
           updInvet.Append("update Stock set Stock_Num=");
           updInvet.Append("(select Stock_Num from Stock where Commodity_Id=@Commodity_Id and WareHose_Id=@WareHose_Id)+(select Por_num from PorBuyMate where Commodity_Id=@Commodity_Id and Pid=@Pid) ");
           updInvet.Append("  where Commodity_Id=@Commodity_Id and WareHose_Id=@WareHose_Id");
           if (listpor != null)
           {
               foreach (PorBuyMate item in listpor)
               {
                   SqlParameter[] updsqlparameter ={
                                                       new SqlParameter("@Commodity_Id",SqlDbType.Int),
                                                       new SqlParameter("@WareHose_Id",SqlDbType.Int),
                                                       new SqlParameter("@Pid",SqlDbType.Int)
                                                   };
                   updsqlparameter[0].Value = item.Commodity_Id;
                   updsqlparameter[1].Value =  erpop(Pid).WareHose_Id;
                   updsqlparameter[2].Value = Pid;
                   cmdTextList.Add(updInvet.ToString());
                   cmdParametersList.Add(updsqlparameter.ToList<SqlParameter>());
               }
           }

           StringBuilder upState = new StringBuilder();
           upState.Append("update PorBuy set Por_Status=1 where Pid=@Pid");
                   SqlParameter[] updsqlparameters ={
                                                       new SqlParameter("@Pid",SqlDbType.Int)
                                                   };
                   updsqlparameters[0].Value = Pid;
                   cmdTextList.Add(upState.ToString());
                   cmdParametersList.Add(updsqlparameters.ToList<SqlParameter>());
           return p.ExecTransaction(cmdTextList, cmdParametersList);
       }

         /// <summary>
        /// 根据采购订单编号获取仓库编号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
       public PorBuy erpop(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select WareHose_Id from PorBuy where Pid=@Pid");

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@Pid",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = id;

            SqlDataReader sdr = p.ExecReader(strSql.ToString(), parametersSub);
            PorBuy ert = new PorBuy();
            while (sdr.Read())
            {
                ert.WareHose_Id = Convert.ToInt32(sdr["WareHose_Id"]);
            }
            return ert;
        }

        /// <summary>
        /// 查看订单子表详细信息
        /// </summary>
        /// <param name="StockOrderID"></param>
        /// <returns></returns>
        public List<PorBuyMate> huoquiry(int PorMaid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Pid,Commodity_Id,Por_num from PorBuyMate where PorMaid=@PorMaid"); 

            SqlParameter[] parametersSub = { 
                                         new SqlParameter("@PorMaid",SqlDbType.Int)  
                                           };
            parametersSub[0].Value = PorMaid;

            DataTable dt = p.ExecDataTable(strSql.ToString(), parametersSub);
            List<PorBuyMate> list = new List<PorBuyMate>();
            foreach (DataRow item in dt.Rows)
            {
                PorBuyMate ps = new PorBuyMate();
                ps.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                ps.Pid = Convert.ToInt32(item["Pid"]);
                ps.Por_num= Convert.ToInt32(item["Por_num"]);

                list.Add(ps);
            }
            return list;
        }
        /// <summary>
        /// 查询某天总交易额
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<PorBuy> tian(string start,string end)
        {
            string sql = "select SUM(PorBuy_SumMoney) as Num,Day(PorBuy_Date) as tdate from PorBuy  group by Day(PorBuy_Date) having Day(PorBuy_Date)>='" + start + "' and Day(PorBuy_Date)<='" + end + "'  order by Day(PorBuy_Date) asc";

            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<PorBuy> list = new List<PorBuy>();
            foreach (DataRow item in table.Rows)
            {
                PorBuy s = new PorBuy();
                s.PorBuy_Date =Convert.ToDateTime(item["Num"].ToString());
                s.Supplier_Name = item["tdate"].ToString();
                list.Add(s);
            }
            return list;
        }
        
        /// <summary>
        /// 查询某月总交易额
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<PorBuy> yue(string start, string end)
        {
            string sql = "select SUM(PorBuy_SumMoney) as Num,MONTH(PorBuy_Date) as tdate from PorBuy  group by MONTH(PorBuy_Date) having MONTH(PorBuy_Date)>='" + start + "' and MONTH(PorBuy_Date)<='" + end + "'  order by MONTH(PorBuy_Date) asc";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<PorBuy> list = new List<PorBuy>();
            foreach (DataRow item in table.Rows)
            {
                PorBuy s = new PorBuy();
                s.PorBuy_Date =Convert.ToDateTime(item["Num"].ToString());
                s.Supplier_Name = item["tdate"].ToString();
                list.Add(s);
            }
            return list;
        }

        /// <summary>
        /// 查询某年总交易额
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List<PorBuy> nian(string start, string end)
        {
            string sql = " select SUM(PorBuy_SumMoney) as Num,Year(PorBuy_Date) as tdate from PorBuy  group by Year(PorBuy_Date) having Year(PorBuy_Date)>='" + start + "' and Year(PorBuy_Date)<='" + end + "'  order by Year(PorBuy_Date) asc";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<PorBuy> list = new List<PorBuy>();
            foreach (DataRow item in table.Rows)
            {
                PorBuy s = new PorBuy();
                s.PorBuy_Date1 =Convert.ToInt32(item["Num"]);
                s.Supplier_Name = item["tdate"].ToString();
                list.Add(s);
            }
            return list;
        }
    }
}
